SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW [dbo].[TTDOCU_ENRE] AS    
	SELECT T1.CO_EMPR, T1.CO_TIPO_DOCU AS 'TI_DOCU_ENRE', ISNULL(T1.NU_DOCU_BANC,'')+ ISNULL(T1.NU_CHEQ,'') AS 'NU_DOCU_ENRE', 
	MAX (T1.TI_ENTI) AS 'TI_ENTI', MAX (T1.CO_ENTI) AS 'CO_ENTI', MAX (T3.NO_AUXI) AS 'DE_NOMB_ORDE',MAX(T1.IM_MOVI) AS 'IM_MOVI'  
	FROM TCMOVI_BANC T1
		Inner Join TTTIPO_OPER T2
		On T1.CO_EMPR = T2.CO_EMPR
		And T1.CO_TIPO_OPER = T2.CO_TIPO_OPER 
		And T2.ST_ENRE_OTRO = 'S'   
		Left Outer Join TMAUXI_EMPR T3
		On T1.CO_EMPR = T3.CO_EMPR    
		AND T1.TI_ENTI = T3.TI_AUXI_EMPR
		AND T1.CO_ENTI = T3.CO_AUXI_EMPR    
	WHERE  T1.TI_MOVI_BANC = 'E'    
		AND T1.TI_SITU != 'ANU'    
	GROUP BY T1.CO_EMPR,T1.CO_TIPO_DOCU, ISNULL(T1.NU_DOCU_BANC,'')+ ISNULL(T1.NU_CHEQ,'') 
UNION ALL    
	SELECT T1.CO_EMPR,T1.TI_DOCU_ENRE, T1.NU_DOCU_ENRE, MAX (T1.TI_ENTI), MAX (T1.CO_ENTI), MAX (T3.NO_AUXI), MAX(T1.IM_MOVI)  
	FROM TCMOVI_CAJA T1 
		Inner Join TTTIPO_OPER T2
		On T1.CO_EMPR = T2.CO_EMPR
		AND T1.CO_TIPO_OPER = T2.CO_TIPO_OPER    
		AND T2.ST_ENRE_OTRO = 'S'    
		Left Outer Join TMAUXI_EMPR T3 
		On T1.CO_EMPR = T3.CO_EMPR    
		AND T1.TI_ENTI = T3.TI_AUXI_EMPR
		AND T1.CO_ENTI = T3.CO_AUXI_EMPR   
	WHERE  T1.TI_MOVI = 'E'    
		AND TI_DOCU_ENRE IS NOT NULL    
		AND T1.TI_SITU != 'ANU'    
	GROUP BY T1.CO_EMPR,T1.TI_DOCU_ENRE, T1.NU_DOCU_ENRE 
UNION ALL    
	SELECT T1.CO_EMPR, 'SOL', ISNULL(T1.NU_SOLI,''), 
		MAX (T1.TI_ENTI), MAX (T1.CO_ENTI), MAX (T3.NO_AUXI), MAX(T1.IM_MOVI)
		FROM TCMOVI_BANC T1
		Inner Join  TTTIPO_OPER T2
		On T1.CO_EMPR = T2.CO_EMPR   
		AND T1.CO_TIPO_OPER = T2.CO_TIPO_OPER    
		AND T2.ST_REPO_CAJA = 'S'
		Left outer Join TMAUXI_EMPR T3
		On T1.CO_EMPR = T3.CO_EMPR    
		AND T1.TI_ENTI = T3.TI_AUXI_EMPR
		AND T1.CO_ENTI = T3.CO_AUXI_EMPR    
	WHERE   T1.TI_MOVI_BANC = 'E'    
		AND T1.TI_SITU != 'ANU'    
		AND LEN(RTRIM(LTRIM(ISNULL(T1.NU_SOLI,'')))) > 0
	GROUP BY T1.CO_EMPR, ISNULL(T1.NU_SOLI,'')
UNION ALL    
	SELECT T1.CO_EMPR, T4.TI_DOCU, t4.NU_DOCU, T4.TI_ENTI, T4.CO_ENTI, MAX (T3.NO_AUXI) AS 'DE_NOMB_ORDE',MAX(T1.IM_MOVI) AS 'IM_MOVI'  
	FROM TCMOVI_BANC T1
		Inner Join TTTIPO_OPER T2
		On T1.CO_TIPO_OPER = T2.CO_TIPO_OPER    
		AND T1.TI_SITU != 'ANU'    
		AND ( T2.ST_REPO_CAJA = 'S' And LEN(RTRIM(LTRIM(ISNULL(T1.NU_SOLI,'')))) = 0 )
		Left Outer Join TMAUXI_EMPR T3
		On T1.CO_EMPR = T3.CO_EMPR 
		Right Outer Join TDMOVI_RECA T4 
		On  T3.TI_AUXI_EMPR =  T4.TI_ENTI 
		AND  T3.CO_AUXI_EMPR =  T4.CO_ENTI   
		WHERE T1.CO_EMPR = T4.CO_EMPR
	    AND T1.CO_BANC = T4.CO_BANC
	    AND T1.NU_CNTA_BANC = T4.NU_CNTA_BANC
	    AND T1.AA_BNCO = T4.AA_BNCO
	    AND T1.MM_BNCO = T4.MM_BNCO
	    AND T1.TI_MOVI_BANC = T4.TI_MOVI_BANC
	    AND T1.NU_COMP_BANC = T4.NU_COMP_BANC
	    AND T1.CO_EMPR = T2.CO_EMPR 
	    AND T1.TI_MOVI_BANC = 'E'    
	    AND EXISTS (	select	T6.ST_DOCU_CNTB 
			from TMCAJA T5, TMPLAN_EMPR T6 
			Where	T4.CO_EMPR_DEST = T5.CO_EMPR 
				and T4.CO_CAJA_DEST = t5.CO_CAJA 
				and T5.CO_EMPR = T6.CO_EMPR 
				And T5.CO_CNTA_EMPR = T6.CO_CNTA_EMPR 
				and T6.ST_DOCU_CNTB = 'S'
		      )
	GROUP BY T1.CO_EMPR, T4.TI_ENTI, T4.CO_ENTI, T4.TI_DOCU, t4.NU_DOCU

GO
